﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Media;
using Infragistics;

using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.QuerySpec;
using SD.LLBLGen.Pro.QuerySpec.Adapter;

using VNet.VisiSuite.Common.Logger;
using VNet.VisiSuite.Service.Contact.Message.Item;
using VNet.VisiSuite.VisiBoard.Dal.DatabaseSpecific;
using VNet.VisiSuite.VisiBoard.Dal.EntityClasses;
using VNet.VisiSuite.VisiBoard.Dal.FactoryClasses;
using VNet.VisiSuite.VisiBoard.Dal.HelperClasses;
using VNet.VisiSuite.Common;
using VNet.VisiSuite.Common.Helper;
using VNet.VisiSuite.Service.Contact.Enum;
using System.Data.SqlClient;
using VNet.VisiSuite.VisiBoard.Models;
using VNet.VisiSuite.Service.Contact.Message.ProductionData;
using VNet.VisiSuite.VisiBoard.Dal;
using System.Data;

namespace VNet.VisiSuite.VisiBoard.BusinessLogic
{
    public class VisiBoard
    {
        #region Private Classes

        // Classes used to store the last ProductionHistory and ProductionHistoryDowntimeEvent in memory.
        private class ProductionHistoryEvent
        {
            private readonly int _production_history_id;
            private int _reason_code_id; // 0 = Good.
            private int? _production_plan_id;
            private readonly DateTime _start_time;
            private readonly int? _key;
            private bool _initial_event; // is this the first event for this shift?
            private bool _offline;
            private bool _no_power;

            private static int _no_power_reason_code_id = 0;

            public ProductionHistoryEvent(int id, int reason_code, int? production_plan_id, DateTime start, int? key, bool offline)
            {
                _production_history_id = id;
                _reason_code_id = reason_code;
                _production_plan_id = production_plan_id;
                _start_time = start;
                _initial_event = false;
                _key = key;
                _offline = offline;

                _no_power = (reason_code == _no_power_reason_code_id);
            }

            public int ProductionHistoryId
            {
                get { return _production_history_id; }
            }

            public int ReasonCodeId
            {
                get { return _reason_code_id; }
                set { _reason_code_id = value; }
            }

            public int? ProductionPlanId
            {
                get { return _production_plan_id; }
                set { _production_plan_id = value; }
            }

            public DateTime StartTime
            {
                get { return _start_time; }
            }

            public int? Key
            {
                get { return _key; }
            }

            public bool Offline
            {
                get { return _offline; }
            }

            public bool InitialEvent
            {
                get { return _initial_event; }
                set { _initial_event = value; }
            }

            public bool NoPower
            {
                get { return _no_power; }
            }

            public static void noPowerReasonCode(int no_power)
            {
                _no_power_reason_code_id = no_power;
            }

            public double elapsedTime()
            {
                double secs = ((TimeSpan)(DateTime.Now - _start_time)).TotalSeconds;
                return secs;
            }


        }

        private class ProductionHistoryDowntimeEvent
        {
            private readonly int _parent_id; // ProductionHistoryID
            private readonly int _reason_code_id; // 0 = Good.
            private readonly DateTime _start_time;
            private readonly int _key;
            private readonly bool _offline;

            public ProductionHistoryDowntimeEvent(int parent, int reason_code, DateTime start, int key, bool offline)
            {
                _parent_id = parent;
                _reason_code_id = reason_code;
                _start_time = start;
                _key = key;
                _offline = offline;
            }

            public int ProductionHistoryId
            {
                get { return _parent_id; }
            }

            public int ReasonCodeId
            {
                get { return _reason_code_id; }
            }

            public DateTime StartTime
            {
                get { return _start_time; }
            }

            public int Key
            {
                get { return _key; }
            }

            public bool Offline
            {
                get { return _offline; }
            }

        }

        #endregion


        public enum MachineState
        {
            None,
            CycleRunning,
            CyclePaused,
            CycleResume,
            CycleEnd,
            Cycle,
            Alarm,
            LoadUnload,
            LoadUnloadTolerance,
            LoadUnloadOverrun,
        }

        public enum UpdateType
        {
            AddNew,
            UpdateLast,
            NewShift,
            PartProduced,
            AddScrap,
            ForceSync,
            Refresh
        }

        public delegate void onProductionHistoryChange(UpdateType ut, DateTime start, DateTime end, int rollup_id);

        #region Member Variables

        static private VisiBoard _instance = null;
        private bool _initialized = false;

        private const int DEFAULT_LOADUNLOAD_TIME = 30;
        private const int DEFAULT_CYCLE_TIME = 60;

        private int _cell_id;
        private string _cell_name;
        private int _area_id;
        private string _area_name;
        private int _production_meter_freq;
        private int _production_meter_width;
        private bool _offline = false;

        private bool _automatic_feed = false;

        private int _cell_shift_id;
        private DateTime _shift_start;
        private DateTime _shift_end;

        // Initialize these at start up.
        private ProductionHistoryEvent _prev_production_history_event = null;
        private ProductionHistoryDowntimeEvent _prev_production_history_downtime_event = null;
        private int _prev_key = 0;
        private DateTime _prev_date = new DateTime(1970, 1, 1);
        private MachineState _prev_state = MachineState.None;
        private long _prev_status = VisiBoxEvent.RUSCO_PART_CATCHER;

        private ReasonCodeEntity _prev_reason = null;
        private ReasonCodeEntity _break_reason = null;
        private ReasonCodeEntity _before_break_reason = null;  // the reason before the user went to break
        private MachineState _before_break_state = MachineState.None;

        private int _loadunload_time = DEFAULT_LOADUNLOAD_TIME;
        private int _cycle_time = DEFAULT_CYCLE_TIME;

        private int? _operator_id = null;
        private string _operator_name;

        private EntityCollection<AlarmSettingEntity> _alarm_settings = null;

        private onProductionHistoryChange _on_production_history_change = null;

        private bool _new_visibox = true;

        #endregion

        #region Properties
        
        public int CellShiftId
        {
            get { return _cell_shift_id; }
        }

        public DateTime ShiftStart
        {
            get { return _shift_start; }
        }

        public DateTime ShiftEnd
        {
            get { return _shift_end; }
        }

        public string CellName
        {
            get { return _cell_name; }
        }

        public int AreaId
        {
            get { return _area_id; }
        }

        public string AreaName
        {
            get { return _area_name; }
        }

        public int ProductionMeterWidth
        {
            get { return _production_meter_width; }
        }

        public int ProductionMeterLabelFreq
        {
            get { return _production_meter_freq; }
        }

        public string BraiderType
        {
            get { return "RUSCO"; }
        }

        public int VisiBoxKey
        {
            get { return _prev_key; }
        }

        public DateTime VisiBoxDate
        {
            get { return _prev_date; }
        }

        public string OperatorName
        {
            get { return _operator_name; }
        }

        public int DowntimeReasonId {
            get { return _prev_production_history_event != null ? _prev_production_history_event.ReasonCodeId : 0; }
        }

        public bool AutomaticFeed
        {
            get { return _automatic_feed; }
        }

        public ReasonCodeEntity Break
        {
            get { return _break_reason; }
        }

        public bool IsOnBreak
        {
            get { return (_break_reason != null && _prev_reason == _break_reason); }
        }


        private void getPreviousReasonCode()
        {
            if (_prev_production_history_event != null)
            {
                _prev_reason = reasonCode(_prev_production_history_event.ReasonCodeId);
                if (_prev_reason != null)
                {
                    if (_prev_reason.Description == "Cycle Running")
                        _prev_state = MachineState.CycleRunning;
                    else if (_prev_reason.Description == "Cycle Resume")
                        _prev_state = MachineState.CycleResume;
                }
            }
        }

        public string DowntimeReasonDesc
        {
            get
            { 
                if (_prev_reason == null)
                    getPreviousReasonCode();
                return _prev_reason != null ? _prev_reason.Description : "";
            }
        }

        public DateTime DowntimeReasonSince
        {
            get { return _prev_production_history_event != null ? _prev_production_history_event.StartTime : ShiftStart; }
        }

        public string DowntimeRollupBackground
        {
            get
            {
                if (_prev_reason == null)
                    getPreviousReasonCode();
                return _prev_reason != null ? _prev_reason.RollupDefinition.Color : "";
            }
        }

        public string DowntimeRollupForeground
        {
            get
            {
                if (_prev_reason == null)
                    getPreviousReasonCode();
                return _prev_reason != null ? _prev_reason.RollupDefinition.FontColor : "";
            }
        }

        public bool IsMachineRunning
        {
            get {  return (_prev_state == MachineState.CycleRunning || _prev_state == MachineState.CycleResume); }
        }

        public bool IsLoadUnload
        {
            get { return (_prev_state == MachineState.LoadUnload); }
        }

        public bool IsLoadUnloadOverrun
        {
            get { return (_prev_state == MachineState.LoadUnloadOverrun); }
        }

        public bool IsOffline
        {
            get { return _offline; }
        }

        public int TargetLoadUnloadTime
        {
            get { return _loadunload_time; }
        }

        public int TargetCycleTime
        {
            get { return _cycle_time; }
        }

        public int AverageLoadUnloadTime
        {
            get { return 0; }
        }

        public int AverageCycleTime
        {
            get { return 0; }
        }

        #endregion



        public VisiBoard()
        {
            initRegistrySettings();
        }

        static public VisiBoard instance()
        {
            return (_instance != null ? _instance : _instance = new VisiBoard());
        }

        public void initRegistrySettings()
        {
            try
            {
                var reg = RegistryHelper.OpenRegistryVisi(CommonConstants.RegistryVisiSuite, true);
                if (reg != null)
                {
                    if (reg.GetValue("HardwarePollInterval") == null) reg.SetValue("HardwarePollInterval", 30);
                    if (reg.GetValue("HardwarePollTimeout") == null) reg.SetValue("HardwarePollTimeout", 9000);
                    if (reg.GetValue("LogDir") == null) reg.SetValue("LogDir", "");
                    if (reg.GetValue("PreScan") == null) reg.SetValue("PreScan", "<scan>");
                    if (reg.GetValue("PostScan") == null) reg.SetValue("PostScan", "</scan>");
                }
                else
                {
                    FileLog.Error("Failed to create registry settings.");
                }
            }
            catch (Exception ex)
            {
                FileLog.Error("Error in initRegistrySettings()", ex);
            }
        }

        public void updateCellStatus()
        {
            try
            {
                if (_cell_id > 0 && _prev_production_history_event != null)
                {
                    using (var adapter = new DataAccessAdapter())
                    {
                        var qf = new QueryFactory();
                        var qry = qf.CellStatus.Where(CellStatusFields.CellId == _cell_id);
                        CellStatusEntity cell_status = adapter.FetchFirst(qry);
                        if (cell_status == null)
                            cell_status = new CellStatusEntity();
                        cell_status.CellId = _cell_id;
                        cell_status.ReasonCodeId = _prev_production_history_event.ReasonCodeId;
                        cell_status.EventTime = _prev_production_history_event.StartTime;
                        cell_status.Offline = _offline;
                        cell_status.Flags = 0;
                        cell_status.Status = 0;
                        if (!adapter.SaveEntity(cell_status))
                            FileLog.Error("updateCellStatus() - error saving cell status.");
                    }
                }
            }
            catch (Exception ex)
            {
                FileLog.Error("updateCellStatus() - ", ex);
            }
        }

        public void clearOldData()
        {
            // Remove synchronized data from previous shifts

            if (_cell_shift_id == 0)
                return;

            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    // ProductionHistoryDowntimeEvent
                    var qf = new QueryFactory();
                    var qry = qf.ProductionHistoryDowntimeEvent
                        .Where(ProductionHistoryDowntimeEventFields.SyncStatus == SyncStatus.InSync);
                    var phdec = adapter.FetchQuery(qry);
                    adapter.DeleteEntityCollection(phdec);

                    // ProductionHistory
                    var qry2 = qf.ProductionHistory
                        .Where(ProductionHistoryFields.SyncStatus == SyncStatus.InSync)
                        .AndWhere(ProductionHistoryFields.CellShiftId != _cell_shift_id)
                        .AndWhere(ProductionHistoryFields.ProductionHistoryId.NotIn(
                            qf.Create().Select(ProductionHistoryDowntimeEventFields.ProductionHistoryId)));
                    var phec = adapter.FetchQuery(qry2);
                    adapter.DeleteEntityCollection(phec);

                    // CellShift
                    var qry4 = qf.CellShift
                        .Where(CellShiftFields.SyncStatus == SyncStatus.InSync)
                        .AndWhere(CellShiftFields.CellShiftId != _cell_shift_id)
                        .AndWhere(CellShiftFields.CellShiftId.NotIn(
                            qf.Create().Select(ProductionHistoryFields.CellShiftId)));
                    var csec = adapter.FetchQuery(qry4);
                    adapter.DeleteEntityCollection(csec);

                }
                catch (Exception ex)
                {
                    FileLog.Error("clearOldData() - ", ex);
                }
            }

        }

        private void getPrevProductionHistory()
        {
            using (var adapter = new DataAccessAdapter())
            {
                try
                {

                    ProductionHistoryEntity phe = null;
                    ProductionHistoryDowntimeEventEntity phdee = null;

                    // Get most recent key.
                    _prev_key = -1;
                    _prev_date = new DateTime(1970, 1, 1);
                    var qf = new QueryFactory();
                    var qry = qf.ProductionHistory
                        .Where(ProductionHistoryFields.VisiBoxKey > 0)
                        .OrderBy(ProductionHistoryFields.VisiBoxKey.Descending());
                    phe = adapter.FetchFirst(qry);
                    if (phe != null)
                    {
                        _prev_key = phe.VisiBoxKey.Value;
                        _prev_date = phe.StartTime;
                    }

                    var qry2 = qf.ProductionHistoryDowntimeEvent
                        .Where(ProductionHistoryDowntimeEventFields.VisiBoxKey > 0)
                        .OrderBy(ProductionHistoryDowntimeEventFields.VisiBoxKey.Descending());
                    phdee = adapter.FetchFirst(qry2);
                    if (phdee != null && phdee.VisiBoxKey > _prev_key)
                    {
                        _prev_key = phdee.VisiBoxKey.Value;
                        _prev_date = phdee.EventTime;
                    }

                    // Get most recent events
                    qry = qf.ProductionHistory
                        .OrderBy(ProductionHistoryFields.StartTime.Descending())
                        .OrderBy(ProductionHistoryFields.ProductionHistoryId.Descending());
                    phe = adapter.FetchFirst(qry);
                    if (phe != null)
                    {
                        _prev_production_history_event = new ProductionHistoryEvent(phe.ProductionHistoryId, phe.ReasonCodeId.HasValue ? phe.ReasonCodeId.Value : 0,
                                                            phe.ProductionPlanId, phe.StartTime, phe.VisiBoxKey, false);
                        operatorId(phe.OperatorId);
                    }

                    qry2 = qf.ProductionHistoryDowntimeEvent
                        .OrderBy(ProductionHistoryDowntimeEventFields.EventTime.Descending())
                        .OrderBy(ProductionHistoryDowntimeEventFields.ProductionHistoryDowntimeEventId.Descending());
                    phdee = adapter.FetchFirst(qry2);
                    if (phdee != null)
                        _prev_production_history_downtime_event = new ProductionHistoryDowntimeEvent(phdee.ProductionHistoryId, phdee.ReasonCodeId.HasValue ? phdee.ReasonCodeId.Value : 0,
                                       phdee.EventTime, phdee.VisiBoxKey.HasValue ? phdee.VisiBoxKey.Value : 0, false);

                    getTargetCycleTimes(_prev_production_history_event != null ? _prev_production_history_event.ProductionPlanId : null);  

                }
                catch (Exception ex)
                {
                    FileLog.Error("getPrevProductionHistory() - ", ex);
                }
            }

        }

        private void getTargetCycleTimes(int? production_plan_id)
        {
            _loadunload_time = DEFAULT_LOADUNLOAD_TIME;
            _cycle_time = DEFAULT_CYCLE_TIME;
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    if (production_plan_id.HasValue)
                    {
                        var qf = new QueryFactory();
                        var qry = qf.Create()
                        .Select(() => new
                        {
                            // ProductOperationTime
                            CycleTime = ProductOperationTimeFields.CycleTime.ToValue<int>(),
                            LoadUnloadTime = ProductOperationTimeFields.LoadUnloadTime.ToValue<int>()
                        })
                        .From(qf.ProductionPlan
                                .InnerJoin(qf.ProductOperationTime).On(ProductOperationTimeFields.ProductId == ProductionPlanFields.ProductId)
                                )
                        .Where(ProductionPlanFields.ProductionPlanId == production_plan_id.Value)
                        .AndWhere(ProductionPlanFields.ProductId == ProductOperationTimeFields.ProductId)
                        .AndWhere(ProductionPlanFields.OperationId == ProductOperationTimeFields.OperationId)
                        .AndWhere(ProductionPlanFields.OperationPass == ProductOperationTimeFields.OperationPass);

                        var data = adapter.FetchFirst(qry);
                        if (data != null)
                        {
                            if (data.LoadUnloadTime > 0)
                                _loadunload_time = data.LoadUnloadTime;
                            if (data.CycleTime > 0)
                                _cycle_time = data.CycleTime;
                        }
                    }
                }
                catch (Exception ex)
                {
                    FileLog.Error("getLoadUnloadTime() - ", ex);
                }
            }

        }

        public void calcAveCycleTime(out double ave_cycle_time, out double ave_loadunload_time)
        {
            ave_cycle_time = ave_loadunload_time = 0;
            int parts_produced = 0;
            var last_category = ReasonCodeItem.ReasonCodeCategory.None;

            int? production_plan_id = _prev_production_history_event != null ? _prev_production_history_event.ProductionPlanId : null;

            getTargetCycleTimes(production_plan_id);

            using (var adapter = new DataAccessAdapter())
            {
                try
                {

                    int[] categories = { (int)ReasonCodeItem.ReasonCodeCategory.CycleTime, (int)ReasonCodeItem.ReasonCodeCategory.LoadUnloadTime, (int)ReasonCodeItem.ReasonCodeCategory.PartProduced };
                    var qf = new QueryFactory();
                    var qry = qf.ProductionHistory
                        .From(QueryTarget.InnerJoin(qf.ReasonCode).On(ProductionHistoryFields.ReasonCodeId == ReasonCodeFields.ReasonCodeId))
                        .WithPath(ProductionHistoryEntity.PrefetchPathReasonCode);

                    if (production_plan_id.HasValue && production_plan_id.Value > 0)
                        qry = qry.Where(ProductionHistoryFields.ProductionPlanId == production_plan_id);
                    else
                        qry = qry.Where(ProductionHistoryFields.ProductionPlanId.IsNull());
                    qry = qry
                        .AndWhere(ReasonCodeFields.Category.In(categories))
                        .AndWhere(ProductionHistoryFields.CellShiftId == _cell_shift_id)
                        .OrderBy(ProductionHistoryFields.StartTime.Ascending())
                        .OrderBy(ProductionHistoryFields.EndTime.Ascending())
                        .OrderBy(ProductionHistoryFields.ProductionHistoryId.Ascending());
                    var phc = adapter.FetchQuery(qry);
                    double secs;

                    //FileLog.Error("calcAveCycleTime() - 100 " + phc.Count);

                    double partial_ave_cycle_time = 0;
                    double partial_ave_loadunload_time = 0;

                    foreach (ProductionHistoryEntity phe in phc)
                    {
                        secs = phe.EndTime.HasValue ? (phe.EndTime.Value - phe.StartTime).TotalSeconds : (DateTime.Now - phe.StartTime).TotalSeconds;
                        last_category = (ReasonCodeItem.ReasonCodeCategory)phe.ReasonCode.Category;
                        switch (last_category)
                        {
                            case ReasonCodeItem.ReasonCodeCategory.CycleTime:
                                partial_ave_cycle_time += secs;
                                break;

                            case ReasonCodeItem.ReasonCodeCategory.LoadUnloadTime:
                                partial_ave_loadunload_time += secs;
                                break;

                            case ReasonCodeItem.ReasonCodeCategory.PartProduced:
                                // Only use full complete cycles in calculation; that is, up to the last PartProduced
                                ave_cycle_time += partial_ave_cycle_time;
                                ave_loadunload_time += partial_ave_loadunload_time;
                                ++parts_produced;
                                partial_ave_cycle_time = 0;
                                partial_ave_loadunload_time = 0;
                                break;
                            default:
                                break;
                        }
                    }

                    if (parts_produced > 0)
                    {
                        ave_cycle_time /= parts_produced;
                        ave_loadunload_time /= parts_produced;
                    }

                }
                catch (Exception ex)
                {
                    FileLog.Error("calcAveCycleTime() - ", ex);
                    ave_cycle_time = ave_loadunload_time = 0;
                }

            }

        }

        public bool isEndShift()
        {
            return DateTime.Now >= _shift_end;
        }

        public void beginNextShift()
        {
            var now = DateTime.Now;

            endShift(_cell_shift_id);
            beginShift(now);
            getPrevProductionHistory();

            if (_on_production_history_change != null)
                _on_production_history_change(UpdateType.NewShift, now, now, 0);
        }

        private void endShift(int cell_shift_id)
        {
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    CellShiftEntity cse = new CellShiftEntity(cell_shift_id);
                    if (adapter.FetchEntity(cse))
                    {
                        var qf = new QueryFactory();
                        var qry = qf.ProductionHistory
                            .Where(ProductionHistoryFields.CellShiftId == cell_shift_id)
                            .AndWhere(ProductionHistoryFields.EndTime.IsNull());
                        var phec = new EntityCollection<ProductionHistoryEntity>();
                        adapter.FetchQuery(qry, phec);
                        if (phec.Count == 1)
                        {
                            phec[0].EndTime = cse.EndTime;
                            if (!phec[0].DowntimeAckTime.HasValue)
                                phec[0].DowntimeAckTime = cse.EndTime;
                            phec[0].SyncStatus = (byte)SyncStatus.OutOfSync;
                            adapter.SaveEntity(phec[0]);
                        }

                    }
                }
                catch (Exception ex)
                {
                    FileLog.Error("endShift() - ", ex);
                }

            }
        }

        private void beginShift(DateTime when)
        {
            var time_of_day = when.TimeOfDay;
            CellShiftEntity current = null;
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    // create a new Cell Shift
                    var qf = new QueryFactory();
                    var qry = qf.AreaShift
                        .Where(AreaShiftFields.AreaId == _area_id);
                    var shifts = new EntityCollection(new AreaShiftEntityFactory());
                    adapter.FetchQuery(qry, shifts);

                    AreaShiftEntity area_shift = null;
                    foreach (AreaShiftEntity ase in shifts)
                    {
                        if (ase.StartTime.TimeOfDay == ase.EndTime.TimeOfDay)
                        {
                            area_shift = ase;
                            break;
                        }
                        else if (ase.StartTime.TimeOfDay < ase.EndTime.TimeOfDay)
                        {
                            if (time_of_day >= ase.StartTime.TimeOfDay && time_of_day < ase.EndTime.TimeOfDay)
                            {
                                area_shift = ase;
                                break;
                            }
                        }
                        else  // shift crosses midnight
                        {
                            var midnight1 = new TimeSpan(24, 0, 0);
                            var midnight2 = new TimeSpan(0, 0, 0);
                            if ((time_of_day >= ase.StartTime.TimeOfDay && time_of_day <= midnight1)
                                || (time_of_day >= midnight2 && time_of_day <= ase.EndTime.TimeOfDay))
                            {
                                area_shift = ase;
                                break;
                            }
                        }
                    }

                    _shift_start = new DateTime(when.Year, when.Month, when.Day, area_shift.StartTime.Hour, area_shift.StartTime.Minute, area_shift.StartTime.Second);
                    if (_shift_start > when)
                        _shift_start = _shift_start.AddDays(-1);
                    _shift_end = new DateTime(when.Year, when.Month, when.Day, area_shift.EndTime.Hour, area_shift.EndTime.Minute, area_shift.EndTime.Second);
                    if (_shift_start >= _shift_end)
                        _shift_end = _shift_end.AddDays(1);

                    current = new CellShiftEntity
                    {
                        CellId = _cell_id,
                        AreaShiftId = area_shift.AreaShiftId,
                        StartTime = _shift_start,
                        EndTime = _shift_end
                    };

                    adapter.SaveEntity(current);
                    _cell_shift_id = current.CellShiftId;

                    // Continue ProductionHistory from previous shift.
                    var qry2 = qf.ProductionHistory
                        .OrderBy(ProductionHistoryFields.StartTime.Descending());
                    ProductionHistoryEntity phe = adapter.FetchFirst(qry2);
                    if (phe != null && phe.EndTime == _shift_start)
                    {
                        var new_phe = new ProductionHistoryEntity {
                            CellShiftId = _cell_shift_id,
                            ReasonCodeId = phe.ReasonCodeId,
                            Operators = phe.Operators,
                            WorkContent = phe.WorkContent,
                            ProductionPlanId = phe.ProductionPlanId,
                            StartTime = _shift_start,
                            DowntimeAckTime = phe.DowntimeAckTime == phe.EndTime ? (DateTime?)null : _shift_start,
                            VisiBoxKey = phe.VisiBoxKey,
                            LastUpdated = DateTime.Now,
                            SyncStatus = (byte)SyncStatus.OutOfSync
                        };
                        adapter.SaveEntity(new_phe);
                    }
                    else
                    {
                        var new_phe = new ProductionHistoryEntity
                        {
                            CellShiftId = _cell_shift_id,
                            ReasonCodeId = reasonCode("Scheduled Down").ReasonCodeId,
                            StartTime = _shift_start,
                            DowntimeAckTime = _shift_start,
                            VisiBoxKey = 0,
                            LastUpdated = DateTime.Now,
                            SyncStatus = (byte)SyncStatus.OutOfSync
                        };
                        adapter.SaveEntity(new_phe);
                    }

                }
                catch (Exception ex)
                {
                    FileLog.Error("beginShift() - ", ex);
                }
            }
        }

#if DEBUGx
       public void createTestData()
        {
           // Delete existing data for the current shift
            try
            {
                using (var adapter = new DataAccessAdapter())
                {
                    var phec = new EntityCollection<ProductionHistoryEntity>();
                    var qf = new QueryFactory();
                    var qry = qf.ProductionHistory
                        .Where(ProductionHistoryFields.CellShiftId == _cell_shift_id);
                    adapter.FetchQuery(qry, phec);
                    adapter.DeleteEntityCollection(phec);

                    var rcec = new EntityCollection<ReasonCodeEntity>();
                    var qry2 = qf.ReasonCode
                        .Where(ReasonCodeFields.AreaId == _area_id);
                    adapter.FetchQuery(qry2, rcec);

                    _prev_production_history_event = null;

                    var rand = new Random();
                    int ii;
                    var now = DateTime.Now;
                    var ss = _shift_start;
                    while (ss < now)
                    {
                        if (_prev_production_history_event != null)
                            updateProductionHistoryEndTime(_prev_production_history_event.ProductionHistoryId, ss); 
                        ii = rand.Next(0, rcec.Count - 1);
                        _prev_production_history_event = addProductionHistoryEvent(rcec[ii].ReasonCodeId, null, ss, 0, rcec[ii].ReasonCodeId == 518, false);
                        ii = rand.Next(1, 6);
                        ss = ss.AddMinutes(ii);
                        //ii = rand.Next(15, 120);
                        //ss = ss.AddSeconds(ii);
                    }

                }
            }
           catch (Exception ex)
            {
                FileLog.Error("createTestData() - ", ex);
            }
        }
#endif

        public void initialize(onProductionHistoryChange on_ph_change)
        {
            _on_production_history_change = on_ph_change;

            if (!_initialized)
            {
                using (var adapter = new DataAccessAdapter())
                {
                    try
                    {
                        var now = DateTime.Now;
                        var qf = new QueryFactory();
                        var qry = qf.Cell
                            .Where(CellFields.CellId != 0)
                            .WithPath(CellEntity.PrefetchPathArea);
                        CellEntity cell = adapter.FetchFirst(qry);

                        _cell_id = cell.CellId;
                        _cell_name = cell.CellName;
                        _area_id = cell.AreaId;
                        _area_name = cell.Area.AreaName;
                        _production_meter_freq = cell.Area.ProductionMeterLabelFreq;
                        _production_meter_width = cell.Area.ProductionMeterWidth;

                        VNet.VisiSuite.VisiBoard.WebServer.SystemInfo.cellName(_cell_name);

                        var qry2 = qf.CellShift
                            .Where(CellShiftFields.CellId == _cell_id)
                            .OrderBy(CellShiftFields.StartTime.Descending());
                        CellShiftEntity cell_shift = adapter.FetchFirst(qry2);
                        if (cell_shift != null && cell_shift.EndTime > now)
                        {
                            _cell_shift_id = cell_shift.CellShiftId;
                            _shift_start = cell_shift.StartTime;
                            _shift_end = cell_shift.EndTime.Value;
                        }
                        else
                        {
                            if (cell_shift != null)
                                endShift(cell_shift.CellShiftId);
                            beginShift(now);
                        }

#if DEBUG
                        //createTestData();
#endif

                        getPrevProductionHistory();

                        clearOldData();

                        _initialized = true;
                    }
                    catch (Exception ex)
                    {
                        FileLog.Error("initialize() - ", ex);
                    }
                }
            }
        }


        public ReasonCodeEntity reasonCode(string desc)
        {
            ReasonCodeEntity rce = null;
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    var qf = new QueryFactory();
                    var qry = qf.ReasonCode
                        .Where(ReasonCodeFields.Description == desc)
                        .AndWhere(ReasonCodeFields.AreaId == _area_id)
                        .AndWhere(ReasonCodeFields.LinkId.IsNull())
                        .WithPath(ReasonCodeEntity.PrefetchPathRollupDefinition);
                    rce = adapter.FetchFirst(qry);
                }
                catch (Exception ex)
                {
                    FileLog.Error("reasonCode() - ", ex);
                }
            }
            return rce;
        }

        public ReasonCodeEntity reasonCode(int id)
        {
            ReasonCodeEntity rce = null;
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    var qf = new QueryFactory();
                    var qry = qf.ReasonCode
                        .Where(ReasonCodeFields.ReasonCodeId == id)
                        .WithPath(ReasonCodeEntity.PrefetchPathRollupDefinition);
                    rce = adapter.FetchFirst(qry);
                }
                catch (Exception ex)
                {
                    FileLog.Error("reasonCode() - ", ex);
                }
            }
            return rce;
        }

        public EntityCollection<AlarmSettingEntity> getAlarmSettings()
        {
           if (_alarm_settings == null || _alarm_settings.Count == 0)
            {
                using (var adapter = new DataAccessAdapter())
                {
                    try
                    {
                        _alarm_settings = new EntityCollection<AlarmSettingEntity>(new AlarmSettingEntityFactory());
                        var qf = new QueryFactory();
                        var qry = qf.AlarmSetting
                            .Where(AlarmSettingFields.AreaId == _area_id);
                        if (IsMachineRunning)
                            qry = qry.OrderBy(AlarmSettingFields.MinPercentage100Good.Ascending());
                        else
                            qry = qry.OrderBy(AlarmSettingFields.MinPercentage100Bad.Ascending());
                        adapter.FetchQuery(qry, _alarm_settings);
                    }
                    catch (Exception ex)
                    {
                        FileLog.Error("alarmColors() - ", ex);
                    }
                }
            }
           else if (IsMachineRunning)
                    _alarm_settings.Sort("MinPercentage100Good", System.ComponentModel.ListSortDirection.Ascending, null);
                else
                    _alarm_settings.Sort("MinPercentage100Bad", System.ComponentModel.ListSortDirection.Ascending, null);

           return _alarm_settings;
        }

        public bool alarmColors(int percentage, bool good_100, out string foreground, out string background)
        {
            foreground = background = string.Empty;
            bool success = false;

            // TO DO CHANGE TO GET ALARM SETTINGS FROM AREA IF NONE DEFINED FOR CELL

            if (_alarm_settings == null || _alarm_settings.Count == 0)
            {
                using (var adapter = new DataAccessAdapter())
                {
                    try
                    {
                        _alarm_settings = new EntityCollection<AlarmSettingEntity>(new AlarmSettingEntityFactory());
                        var qf = new QueryFactory();
                        var qry = qf.AlarmSetting
                            .Where(AlarmSettingFields.AreaId == _area_id);
                        if (good_100)
                            qry = qry.OrderBy(AlarmSettingFields.MinPercentage100Good.Ascending());
                        else
                            qry = qry.OrderBy(AlarmSettingFields.MinPercentage100Bad.Ascending());
                        adapter.FetchQuery(qry, _alarm_settings);
                    }
                    catch (Exception ex)
                    {
                        FileLog.Error("alarmColors() - ", ex);
                    }
                }

            }

            if (_alarm_settings != null && _alarm_settings.Count > 0)
            {
                success = true;
                foreground = _alarm_settings.First().FontColor;
                background = _alarm_settings.First().Color;

                if (good_100 == true)
                    _alarm_settings.Sort("MinPercentage100Good", System.ComponentModel.ListSortDirection.Ascending, null);
                else
                    _alarm_settings.Sort("MinPercentage100Bad", System.ComponentModel.ListSortDirection.Ascending, null);

                foreach (AlarmSettingEntity ase in _alarm_settings)
                {
                    if ((good_100 == true && percentage >= ase.MinPercentage100Good) || (good_100 == false && percentage >= ase.MinPercentage100Bad))
                    {
                        foreground = ase.FontColor;
                        background = ase.Color;
                    }
                    else
                    {
                        break;
                    }
                }
            }

            return success;
        }


        public bool processVisiBoxEvent(VisiBoxEvent evt)
        {
            bool success = true;

            if (evt.isOfflineEvent())
            {
                _offline = evt.offline();
                VNet.VisiSuite.VisiBoard.WebServer.SystemInfo.offline(_offline);
                FileLog.Error("processVisiBoxEvent() - offline = " + (evt.offline() ? "1" : "0"));
                return false;

            }
            else if (evt.key() <= _prev_key && evt.time() < _prev_date)
            {
                return false;

            }
            else if (evt.time() < _shift_start)
            {
                // FIX THIS PROCESS EVENTS FOR PREVIOUS SHIFT
                _prev_key = evt.key();
                return false;

            }
            else if (evt.time() > _shift_end)
            {
                beginNextShift();
            }

            string reason = "";
            bool track_downtime_ack = false;
            MachineState state = MachineState.None;

            if (_new_visibox)
            {
                if ((evt.status() & VisiBoxEvent.RUSCO_ALARM) > 0)
                {
                    reason = "Alarm";
                    state = MachineState.Alarm;
                }
                else if ((evt.status() & VisiBoxEvent.RUSCO_CYCLE_END) > 0 && _prev_state == MachineState.CycleRunning)
                {
                    _automatic_feed = false;
                    reason = "Cycle End";
                    state = MachineState.CycleEnd;
                }
                else if ((evt.status() & VisiBoxEvent.RUSCO_PART_CATCHER) > 0 && ((_prev_status & VisiBoxEvent.RUSCO_PART_CATCHER) == 0)  && _prev_state == MachineState.CycleRunning)
                {
                    _automatic_feed = true;
                    reason = "Cycle End";
                    state = MachineState.CycleEnd;
                }
                else if ((evt.status() & VisiBoxEvent.RUSCO_COOLANT) > 0)
                {
                    reason = "Cycle Running";
                    state = MachineState.CycleRunning;
                }
                else
                {
                    state = _prev_state;
                }
            }
            else
            {

                if (evt.status() == 0)
                {
                    reason = "Load / Unload";
                    state = MachineState.LoadUnload;
                    track_downtime_ack = false;
                }
                else if (evt.status() == VisiBoxEvent.RUSCO_COOLANT)
                {
                    reason = "Cycle Paused";
                    state = MachineState.CyclePaused;
                }
                else if (evt.status() == (VisiBoxEvent.RUSCO_COOLANT + VisiBoxEvent.RUSCO_CYCLE))
                {
                    if (_prev_state == MachineState.CyclePaused)
                    {
                        reason = "Cycle Resume";
                        state = MachineState.CycleResume;
                    }
                    else
                    {
                        reason = "Cycle Running";
                        state = MachineState.CycleRunning;
                    }
                }
                else
                {
                    track_downtime_ack = true;
                    reason = evt.reason(0);
                    if (evt.status() == VisiBoxEvent.RUSCO_CYCLE)
                        state = MachineState.Cycle;
                    else
                        state = MachineState.Alarm;
                }

            }

            int? production_plan_id = null;
            ReasonCodeEntity new_reason = null;
            
            if (_prev_production_history_event == null)
            {
                // start of shift
                new_reason = reasonCode(reason);
                _prev_production_history_event = addProductionHistoryEvent(new_reason, production_plan_id, evt.time(), evt.key(), track_downtime_ack, evt.offline());
                _prev_production_history_downtime_event = null;

            } 
            /**
            else if (!IsMachineRunning && !evt.isRunning())
            {
                // multiple downtimes in a row
                _prev_production_history_downtime_event = addProductionHistoryDowntimeEvent(_prev_production_history_event.ProductionHistoryId, reasonCode(reason), evt.time(), evt.key(), evt.offline());
            }
            **/
            else if ((_prev_reason != null && _prev_reason.IgnoreVisiBoxEvents)|| state == _prev_state
                    || (reason == "Load / Unload" && (_prev_state == MachineState.LoadUnload || _prev_state == MachineState.LoadUnloadOverrun ||_prev_state == MachineState.LoadUnloadTolerance)))
            {
                addProductionHistoryDowntimeEvent(_prev_production_history_event.ProductionHistoryId, reasonCode(reason), evt.time(), evt.key(), evt.offline());
                // don't change state
                state = _prev_state;
                success = false;

                if (state == MachineState.LoadUnloadOverrun && _on_production_history_change != null)
                    _on_production_history_change(UpdateType.ForceSync, DateTime.Now, DateTime.Now, -1);
            }
            else if (state != _prev_state)
            {
                production_plan_id = _prev_production_history_event.ProductionPlanId;
                updateProductionHistoryEndTime(_prev_production_history_event.ProductionHistoryId, evt.time());
       
                if (state == MachineState.CycleEnd || (state == MachineState.Cycle && (_prev_state == MachineState.CycleRunning || _prev_state == MachineState.CycleResume)))
                {
                    // PART PRODUCED
                    _prev_production_history_event = addProductionHistoryEvent(reasonCode("Part Produced"), production_plan_id, evt.time(), evt.key(), false, false);
                    updateProductionHistoryEndTime(_prev_production_history_event.ProductionHistoryId, evt.time());

                    new_reason = reasonCode("Load / Unload");
                    _prev_production_history_event = addProductionHistoryEvent(new_reason, production_plan_id, evt.time(), 0, false, evt.offline());
                    _prev_production_history_downtime_event = null;
                    state = MachineState.LoadUnload;

                    if (!_automatic_feed && _break_reason != null)
                    {
                        // switch to break
                        updateProductionHistoryEndTime(_prev_production_history_event.ProductionHistoryId, evt.time());
                        _before_break_reason = new_reason;
                        _before_break_state = _prev_state;
                        state = MachineState.None;
                        new_reason = _break_reason;
                        _prev_production_history_event = addProductionHistoryEvent(new_reason, production_plan_id, evt.time(), 0, false, evt.offline());
                        _prev_production_history_downtime_event = null;
                    }

                    if (_on_production_history_change != null)
                        _on_production_history_change(UpdateType.PartProduced, evt.time(), evt.time(), 1);
                }
                else
                {

                    if (_break_reason != null && state == MachineState.Alarm)
                    {
                        // switch to break
                        _before_break_reason = reasonCode(reason);
                        _before_break_state = _prev_state;
                        state = MachineState.None;
                        new_reason = _break_reason;
                    }
                    else
                    {
                        new_reason = reasonCode(reason);
                    }
                    _prev_production_history_event = addProductionHistoryEvent(new_reason, production_plan_id, evt.time(), evt.key(), track_downtime_ack, evt.offline());
                    _prev_production_history_downtime_event = null;
                }

            }

            _prev_state = state;
            if (new_reason != null)
                _prev_reason = new_reason;
            _prev_status = evt.status();
            _prev_key = evt.key();
            _prev_date = evt.time();
            
            return success;

        }

        public bool updateStateToLoadUnloadTolerance(int seconds_offset)
        {
            var success = false;

            if (_prev_state == MachineState.LoadUnload)
            {
                var when = _prev_production_history_event.StartTime.AddSeconds(seconds_offset);
                updateProductionHistoryEndTime(_prev_production_history_event.ProductionHistoryId, when, false);

                _prev_reason = reasonCode("Load / Unload Tolerance");

                _prev_production_history_event = addProductionHistoryEvent(_prev_reason, _prev_production_history_event.ProductionPlanId, when, 0, false, false);
                _prev_production_history_downtime_event = null;

                _prev_state = MachineState.LoadUnloadTolerance;

                success = true;
            }

            return success;

        }


        public bool updateStateToLoadUnloadOverrun(int seconds_offset)
        {
            var success = false;

            if (_prev_state == MachineState.LoadUnloadTolerance)
            {
                var when = _prev_production_history_event.StartTime.AddSeconds(seconds_offset);
                updateProductionHistoryEndTime(_prev_production_history_event.ProductionHistoryId, when, false);

                _prev_reason = reasonCode("Load / Unload Overrun");

                _prev_production_history_event = addProductionHistoryEvent(_prev_reason, _prev_production_history_event.ProductionPlanId, when, 0, true, false);
                _prev_production_history_downtime_event = null;

                _prev_state = MachineState.LoadUnloadOverrun;

                if (_on_production_history_change != null)
                    _on_production_history_change(UpdateType.ForceSync, DateTime.Now, DateTime.Now, -1);

                success = true;
            }

            return success;

        }

        public void refreshPrevProductionHistory()
        {
            if (_on_production_history_change != null && _prev_production_history_event != null)
                _on_production_history_change(UpdateType.UpdateLast, _prev_production_history_event.StartTime, DateTime.Now, -1);
        }

        public void operatorId(int? operator_id)
        {
            _operator_id = operator_id;
            if (_operator_id.HasValue)
            {
                try
                {
                    using (var adapter = new DataAccessAdapter())
                    {
                        var oe = new OperatorEntity(operator_id.Value);
                        if (adapter.FetchEntity(oe))
                        {
                            _operator_name = "";
                            if (!string.IsNullOrWhiteSpace(oe.FirstName))
                                _operator_name = oe.FirstName;
                          
                            if (!string.IsNullOrWhiteSpace(oe.LastName))
                            {
                                if (!string.IsNullOrWhiteSpace(_operator_name))
                                    _operator_name += " ";
                                _operator_name = oe.LastName;
                            }
                        }
                        else
                        {
                            _operator_id = null;
                            _operator_name = "";
                        }
                    }
                }
                catch (Exception e)
                {
                    FileLog.Error("operatorId() - ", e);
                    _operator_id = null;
                    _operator_name = "";
                }

            }
            else
            {
                _operator_name = "";
            }
        }

        public void endBreak()
        {
            if (_prev_reason == _break_reason)
            {
                _break_reason = null;
                reasonSelected(_before_break_reason, null, true);
                _prev_state = _before_break_state;
                FileLog.Error("endBreak() - " + _before_break_reason.Description);
            }
            _break_reason = null;
            _before_break_reason = null;
            _before_break_state = MachineState.None;
        }

        public void reasonSelected(ReasonCodeEntity reason_code, int? production_plan_id, bool new_reason)
        {

            if (reason_code.RollupDefinition == null)
                reason_code = reasonCode(reason_code.ReasonCodeId);

            if (reason_code.Category == (short)ReasonCodeItem.ReasonCodeCategory.Break)
            {
                if (_break_reason != null)  // DO NOT ALLOW TO GO FROM ONE BREAK REASON TO ANOTHER
                    return;

                _before_break_reason = _prev_reason;
                _before_break_state = _prev_state;
                _break_reason = reason_code;
                reason_code.IgnoreVisiBoxEvents = _before_break_reason.IgnoreVisiBoxEvents;

                bool change_to_break = false;
                switch ((ReasonCodeItem.ReasonCodeCategory)_prev_reason.Category)
                {
                    case ReasonCodeItem.ReasonCodeCategory.SetupTime:
                    case ReasonCodeItem.ReasonCodeCategory.DownTime:
                    case ReasonCodeItem.ReasonCodeCategory.ScheduledDown:
                        change_to_break = true;
                        break;

                    case ReasonCodeItem.ReasonCodeCategory.LoadUnloadTime:
                        if (!_automatic_feed)
                            change_to_break = true;
                        break;

                    default:
                        change_to_break = false;
                        break;
                }

                if (!change_to_break)
                    return;
            }

            _prev_reason = reason_code;
            _prev_state = MachineState.None;

            //if (_prev_state == MachineState.LoadUnloadOverrun || _prev_state == MachineState.LoadUnloadTolerance)
            //    _prev_state = MachineState.LoadUnload;

            if (!production_plan_id.HasValue || production_plan_id == 0)
            {
                production_plan_id = _prev_production_history_event.ProductionPlanId;
            }
            else
            {
                // production plan changed, update load / unload time, cycle time
                getTargetCycleTimes(production_plan_id);      
            }

            if (new_reason)
            {
                DateTime now = DateTime.Now;
                // The times may be off from VisiBoard and VisiBox.  Make sure that "now" is greater than the StartTime of the previous record
                TimeSpan diff = now.Subtract(_prev_production_history_event.StartTime);
                if (diff.TotalSeconds <= 0)
                    now = _prev_production_history_event.StartTime;

                // Update end time of previous record and add a "new downtime" record
                updateProductionHistoryEndTime(_prev_production_history_event.ProductionHistoryId, now);
                _prev_production_history_event = addProductionHistoryEvent(reason_code, production_plan_id, now, 0, false, _prev_production_history_event.Offline);
                _prev_production_history_downtime_event = null;
            }
            else
            {
                // Update ReasoncodeID
                _prev_production_history_event.ReasonCodeId = reason_code.ReasonCodeId;
                _prev_production_history_event.ProductionPlanId = production_plan_id;
                updateProductionHistoryReasonCode(_prev_production_history_event.ProductionHistoryId, reason_code, production_plan_id);
            }

            if (_on_production_history_change != null)
            {
                _on_production_history_change(UpdateType.ForceSync, DateTime.Now, DateTime.Now, -1);
            }


        }

        public void addScrap(int pp_id, DateTime when, double qty, ReasonCodeEntity scrap_reason)
        {
            try
            {
                using (var adapter = new DataAccessAdapter())
                {
                    var entity = new ProductionHistoryEntity();
                    entity.ReasonCodeId = scrap_reason.ReasonCodeId;
                    entity.ProductionPlanId = pp_id;
                    entity.CellShiftId = _cell_shift_id;
                    entity.WorkContent = (decimal)qty;
                    entity.OperatorId = _operator_id;
                    entity.EndTime = entity.StartTime = when;
                    entity.LastUpdated = DateTime.Now;
                    entity.SyncStatus = (byte)SyncStatus.OutOfSync;
                    adapter.SaveEntity(entity);
                }
                if (_on_production_history_change != null)
                    _on_production_history_change(UpdateType.AddScrap, when, when, (int)qty);
            }
            catch (Exception e)
            {
                FileLog.Error("scrap() - ", e);
            }
        }

        public List<DowntimeGraphDataItem> GetDowntimeChartData()
        {
            var entities = new List<DowntimeGraphDataItem>();

            try
            {
                using (var adapter = new DataAccessAdapter())
                {
                    var qf = new QueryFactory();
                    var query = qf.Create()
                        .Select(() => new DowntimeGraphDataItem
                        {
                            Color = RollupDefinitionFields.Color.Source("rd").ToValue<string>(),
                            Description = ReasonCodeFields.Description.Source("rc").ToValue<string>(),
                            StartTime = ProductionHistoryFields.StartTime.Source("ph").ToValue<DateTime>(),
                            EndTime = ProductionHistoryFields.EndTime.Source("ph").ToValue<DateTime?>(),
                            DowntimeAckTime = ProductionHistoryFields.DowntimeAckTime.Source("ph").ToValue<DateTime?>(),
                            IgnoreInWaitingCalc = ReasonCodeFields.IgnoreInWaitingCalc.Source("rc").ToValue<bool>()
                        })
                        .From(
                            qf.ProductionHistory.As("ph")
                                .InnerJoin(ProductionHistoryEntity.Relations.ReasonCodeEntityUsingReasonCodeId, "ph", "rc")
                                .InnerJoin(ReasonCodeEntity.Relations.RollupDefinitionEntityUsingRollupDefinitionId, "rc", "rd")
                            )
                        .Where(
                            (ProductionHistoryFields.CellShiftId.Source("ph") == _cell_shift_id).And(
                                ReasonCodeFields.DisplayInDowntimeChart.Source("rc") == 1));

                    var val = adapter.FetchQuery(query);

                    return val;
                }
            }
            catch (Exception e)
            {
                FileLog.Error("GetDowntimeChartData() - ", e);
            }

            return entities;
        } 

        public IEnumerable<dynamic> getProductionPlanData()
        {
            var list = new List<dynamic>();
            try
            {
                using (var adapter = new DataAccessAdapter())
                {
                    var qf = new QueryFactory();
                    var query = qf.Create()
                    .Select(() => new
                    {
                        // Production Plan
                        ProductionPlanId = ProductionPlanFields.ProductionPlanId.ToValue<int>(),
                        ProductId = ProductionPlanFields.ProductId.ToValue<int>(),
                        WorkOrder = ProductionPlanFields.WorkOrder.ToValue<string>(),
                        OperationId = ProductionPlanFields.OperationId.ToValue<int?>(),
                        OperationPass = ProductionPlanFields.OperationPass.ToValue<short?>(),
                        SetupTime = ProductOperationTimeFields.SetupTime.ToValue<int>(),
                        Planned = ProductionPlanFields.Planned.ToValue<int>(),
                        Actual = ProductionPlanFields.Actual.ToValue<int>(),
                        ActualDate = ProductionPlanFields.ActualDate.ToValue<DateTime?>(),

                        // Product
                        Sku = ProductFields.Sku.ToValue<string>(),
                    })
                    .From(qf.ProductionPlan
                            .InnerJoin(qf.Product).On(ProductionPlanFields.ProductId == ProductFields.ProductId)
                            .InnerJoin(qf.ProductOperationTime).On((ProductionPlanFields.OperationId == ProductOperationTimeFields.OperationId)
                                                                    .And(ProductionPlanFields.OperationPass == ProductOperationTimeFields.OperationPass)
                                                                    .And(ProductionPlanFields.ProductId == ProductOperationTimeFields.ProductId))
                            )
                    .Where(ProductionPlanFields.CellId == _cell_id)
                    .AndWhere(ProductionPlanFields.Closed == 0)
                    .AndWhere(ProductionPlanFields.Enabled == 1)
                    .OrderBy(ProductionPlanFields.SortOrder.Ascending())
                    .OrderBy(ProductionPlanFields.DueDate.Descending());

                    list = adapter.FetchQuery(query).ToList<dynamic>();
                }
            }
            catch (Exception e)
            {
                FileLog.Error("getProductionPlanData() - ", e);
            }

            return list;

        }

        public bool validateOperator(string operator_code, int production_plan_id, out string err_msg, out OperatorEntity oe)
        {

            bool success = false;
            oe = null;
            err_msg = "";

            try
            {
                using (var adapter = new DataAccessAdapter())
                {
                    var qf = new QueryFactory();
                    var op_qry = qf.Operator.Where(OperatorFields.EmployeeCode == operator_code);
                    oe = adapter.FetchFirst(op_qry);

                    var ppe_qry = qf.ProductionPlan.WithPath(ProductionPlanEntity.PrefetchPathOperation).Where(ProductionPlanFields.ProductionPlanId == production_plan_id);
                    var ppe = adapter.FetchFirst(ppe_qry);
                    if (production_plan_id > 0 && ppe == null)
                    {
                        success = false;
                        err_msg = "Invalid job selected.";
                    }

                    if (oe == null)
                    {
                        err_msg = "Invalid operator code.";
                        success = false;
                    }

                    if (ppe != null && oe != null)
                    {
                        var oa_qry = qf.OperatorAuth.Where(OperatorAuthFields.OperatorId == oe.OperatorId).AndWhere(OperatorAuthFields.OperationId == ppe.OperationId);
                        var oa = adapter.FetchFirst(oa_qry);
                        success = true;
                        if (oa == null)
                        {
                            oe = null;
                            success = false;
                            err_msg = string.Format("Operator {0} is not authorized for operation {1}.", operator_code, ppe.Operation != null ? ppe.Operation.Description : "unknown");
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                FileLog.Error("validateOperator() - ", ex);
                success = false;
            }

            if (success == false)
                oe = null;

            return success;
        }

        public List<dynamic> currentAndNextProductionPlans()
        {
            var list = new List<dynamic>();
            try
            {

                int? production_plan_id = (_prev_production_history_event != null) ? _prev_production_history_event.ProductionPlanId : null;

                using (var adapter = new DataAccessAdapter())
                {
                    var qf = new QueryFactory();
                    var query = qf.Create()
                    .Select(() => new
                    {
                        // Production Plan
                        ProductionPlanId = ProductionPlanFields.ProductionPlanId.Source("pp").ToValue<int>(),
                        ProductId = ProductionPlanFields.ProductId.Source("pp").ToValue<int>(),
                        WorkOrder = ProductionPlanFields.WorkOrder.Source("pp").ToValue<string>(),
                        OperationId = ProductionPlanFields.OperationId.Source("pp").As("wof1").ToValue<int?>(),
                        OperationPass = ProductionPlanFields.OperationPass.Source("pp").As("wof2").ToValue<short?>(),
                        Planned = ProductionPlanFields.Planned.Source("pp").ToValue<int>(),
                        Actual = ProductionPlanFields.Actual.Source("pp").ToValue<int>(),
                        ActualDate = ProductionPlanFields.ActualDate.Source("pp").ToValue<DateTime?>(),

                        // Product
                        Sku = ProductFields.Sku.Source("p").ToValue<string>(),

                        // ProductOperationTime
                        SetupTime = ProductOperationTimeFields.SetupTime.Source("pot").ToValue<int>(),
                        PartTime = ProductOperationTimeFields.PartTime.Source("pot").ToValue<int>(),
                        CycleTime = ProductOperationTimeFields.CycleTime.Source("pot").ToValue<int>(),
                        LoadUnloadTime = ProductOperationTimeFields.LoadUnloadTime.Source("pot").ToValue<int>(),
                    })
                    .From(qf.ProductionPlan.As("pp")
                            .InnerJoin(qf.ProductOperationTime.As("pot")).On((ProductOperationTimeFields.ProductId.Source("pot") == ProductionPlanFields.ProductId.Source("pp"))
                                                                             .And(ProductOperationTimeFields.OperationId.Source("pot") == ProductionPlanFields.OperationId.Source("pp"))
                                                                             .And(ProductOperationTimeFields.OperationPass.Source("pot") == ProductionPlanFields.OperationPass.Source("pp")))
                            .InnerJoin(qf.Product.As("p")).On(ProductionPlanFields.ProductId.Source("pp") == ProductFields.ProductId.Source("p"))
                            )
                    .Where(ProductionPlanFields.CellId.Source("pp") == _cell_id)
                    .AndWhere(ProductionPlanFields.Closed.Source("pp") == 0)
                    .AndWhere(ProductionPlanFields.Enabled.Source("pp") == 1)
                    .OrderBy(ProductionPlanFields.DueDate.Source("pp").Descending())
                    .OrderBy(ProductionPlanFields.SortOrder.Source("pp").Ascending());

                    var items = adapter.FetchQuery(query);

                    if (items.Any() && production_plan_id.HasValue)
                    {
                        foreach (var item in items)
                        {
                            if (item.ProductionPlanId == production_plan_id.Value)
                            {
                                list.Add(item);
                            }
                            else if (list.Any())
                            {
                                list.Add(item);
                                break;
                                }
                        }
                        // If the last one is selected, then make the first one if it exists the next one
                        if (list.Count() == 1 && items[0].ProductionPlanId != items[0].ProductionPlanId)
                        {
                            list.Add(items[0]);
                        }
                    }

                    if (!list.Any())
                    {
                        list.Add(null);
                        list.Add(items.Any() ? items[0] : null);
                    }

                }
            }
            catch (Exception e)
            {
                FileLog.Error("currentAndNextProductionPlans() - ", e);
            }

            return list;
        }

        public int goodAndScrap(int? production_plan_id, DateTime? after, out int scrap)
        {
            int good = 0;
            scrap = 0;

            if (production_plan_id.HasValue) 
            {
                try
                {
                    using (var adapter = new DataAccessAdapter())
                    {
                        var qf = new QueryFactory();
                        var qry = qf.Create()
                            .Select(() => new
                            {
                                Id = ProductionHistoryFields.ProductionHistoryId.ToValue<int>(),
                                WorkContent = ProductionHistoryFields.WorkContent.ToValue<decimal?>(),
                                Category = ReasonCodeFields.Category.ToValue<short>()
                            })
                            .From(qf.ProductionHistory.InnerJoin(qf.ReasonCode).On(ProductionHistoryFields.ReasonCodeId == ReasonCodeFields.ReasonCodeId))
                            .Where(ProductionHistoryFields.ProductionPlanId == production_plan_id)
                            .AndWhere((ReasonCodeFields.Category == (short)ReasonCodeItem.ReasonCodeCategory.PartProduced)
                                       .Or(ReasonCodeFields.Scrap == true));
                        if (after.HasValue)
                            qry = qry.AndWhere(ProductionHistoryFields.StartTime > after);

                        var items = adapter.FetchQuery(qry);
                        int cnt;
                        foreach (var ph in items)
                        {
                            cnt = ph.WorkContent.HasValue ? (int)ph.WorkContent.Value : 1;
                            if (ph.Category == (short)ReasonCodeItem.ReasonCodeCategory.PartProduced)
                                good += cnt;
                            else
                                scrap += cnt;
                        }

                    }
                }
                catch (Exception e)
                {
                    FileLog.Error("goodAndScrap() - ", e);
                }
            }
            return good;
        }

        // Add a new ProductionHistoryEntity with the given ReasonCodeID, StartTime, and Key
        private ProductionHistoryEvent addProductionHistoryEvent(ReasonCodeEntity reason, int? production_plan_id, DateTime start, int? key, bool track_downtime_ack, bool offline)
        {
            int id = 0;

            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    // Note reason_id == 0 means "good"; database entry should be NULL
                    // Save new Entity to database, assign to id
                    ProductionHistoryEntity entity = new ProductionHistoryEntity();
                    entity.CellShiftId = _cell_shift_id;
                    entity.WorkContent = (reason != null && reason.Category == (short)ReasonCodeItem.ReasonCodeCategory.PartProduced) ? 1m : (decimal?)null;
                    entity.Operators = 1;

                    entity.ReasonCodeId = reason != null ? reason.ReasonCodeId : (int?)null;

                    if (production_plan_id.HasValue && production_plan_id > 0)
                        entity.ProductionPlanId = production_plan_id;
                    else
                        entity.ProductionPlanId = null;

                    if (!track_downtime_ack)
                        entity.DowntimeAckTime = start; // zero time waiting for operator
                    entity.StartTime = start;
                    entity.EndTime = null;
                    entity.OperatorId = _operator_id;
                    entity.VisiBoxKey = key;
                    entity.LastUpdated = DateTime.Now;
                    entity.SyncStatus = (byte)SyncStatus.OutOfSync;
                    adapter.SaveEntity(entity);

                    id = entity.ProductionHistoryId;

                    if (_on_production_history_change != null)
                    {
                        _on_production_history_change(UpdateType.AddNew, start, DateTime.Now, reason != null ? (int)reason.RollupDefinitionId : 0);
                    }

                }
                catch (Exception ex)
                {
                    FileLog.Error("addProductionHistoryEvent() - ", ex);
                }
            }

            return new ProductionHistoryEvent(id, reason != null ? reason.ReasonCodeId : 0, production_plan_id, start, key, offline);
        }

        // Update the EndTime of the ProductionHistoryEntity with the give ProductionHistoryID
        private void updateProductionHistoryEndTime(int production_history_id, DateTime end, bool check_load_unload = true)
        {
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    // NOTE IF THERE IS A VALID ReasonCodeID for this item and DowntimeAckTime is null, then we need to set DowntimeAckTime = EndTime
                    DateTime start;

                    ProductionHistoryEntity entity = new ProductionHistoryEntity(production_history_id);
                    IPrefetchPath2 prefetch = new PrefetchPath2(EntityType.ProductionHistoryEntity);
                    prefetch.Add(ProductionHistoryEntity.PrefetchPathReasonCode);
                    adapter.FetchEntity(entity, prefetch);
                    if (!entity.DowntimeAckTime.HasValue || entity.DowntimeAckTime > end)
                    {
                        entity.DowntimeAckTime = end;
                    }

                    start = entity.StartTime;
                    entity.EndTime = end;
                    entity.LastUpdated = DateTime.Now;
                    entity.SyncStatus = (byte)SyncStatus.OutOfSync;

                    var how_to_update = UpdateType.UpdateLast;

                    if (check_load_unload && entity.ReasonCode.Category == (short)ReasonCodeItem.ReasonCodeCategory.LoadUnloadTime && entity.ReasonCode.Description == "Load / Unload")
                    {

                        // Find load unload time
                        int load_unload_time = DEFAULT_LOADUNLOAD_TIME;
                        if (entity.ProductionPlanId.HasValue && entity.ProductionPlanId > 0)
                        {
                            var qf = new QueryFactory();
                            var query = qf.Create()
                            .Select(() => new
                            {
                                ProductionPlanId = ProductionPlanFields.ProductionPlanId.ToValue<int>(),
                                LoadUnloadTime = ProductOperationTimeFields.LoadUnloadTime.ToValue<int>()
                            })
                            .From(qf.ProductionPlan
                                    .InnerJoin(qf.ProductOperationTime).On((ProductOperationTimeFields.ProductId == ProductionPlanFields.ProductId)
                                                                            .And(ProductOperationTimeFields.OperationId == ProductionPlanFields.OperationId)
                                                                            .And(ProductOperationTimeFields.OperationPass == ProductionPlanFields.OperationPass))
                                    )
                            .Where(ProductionPlanFields.ProductionPlanId == entity.ProductionPlanId.Value);

                            var data = adapter.FetchFirst(query);
                            if (data != null && data.LoadUnloadTime > 0)
                            {
                                load_unload_time = data.LoadUnloadTime;
                            }
                        }

                        double diff = (end - start).TotalSeconds;
                        if (diff > load_unload_time)
                        {
                            entity.EndTime = start.AddSeconds(load_unload_time);

                            var tolerance = new ProductionHistoryEntity();
                            tolerance.StartTime = entity.EndTime.Value;
                            tolerance.EndTime = tolerance.StartTime.AddSeconds(0.1 * load_unload_time);
                            tolerance.CellShiftId = entity.CellShiftId;
                            tolerance.DowntimeAckTime = tolerance.StartTime;
                            tolerance.OperatorId = entity.OperatorId;
                            tolerance.ProductionPlanId = entity.ProductionPlanId;
                            tolerance.ReasonCodeId = reasonCode("Load / Unload Tolerance").ReasonCodeId;
                            tolerance.SyncStatus = (byte)SyncStatus.OutOfSync;
                            tolerance.LastUpdated = DateTime.Now;

                            if (tolerance.EndTime > end)
                            {
                                tolerance.EndTime = end;

                                adapter.StartTransaction(IsolationLevel.ReadCommitted, "updateProductionHistoryEndTime");
                                adapter.SaveEntity(entity);
                                adapter.SaveEntity(tolerance);
                                adapter.Commit();
                            }
                            else
                            {

                                var overrun = new ProductionHistoryEntity();
                                overrun.StartTime = tolerance.EndTime.Value;
                                overrun.EndTime = end;
                                overrun.CellShiftId = entity.CellShiftId;
                                overrun.DowntimeAckTime = overrun.StartTime;
                                overrun.OperatorId = entity.OperatorId;
                                overrun.ProductionPlanId = entity.ProductionPlanId;
                                overrun.ReasonCodeId = reasonCode("Load / Unload Overrun").ReasonCodeId;
                                overrun.SyncStatus = (byte)SyncStatus.OutOfSync;
                                overrun.LastUpdated = DateTime.Now;

                                adapter.StartTransaction(IsolationLevel.ReadCommitted, "updateProductionHistoryEndTime");
                                adapter.SaveEntity(entity);
                                adapter.SaveEntity(tolerance);
                                adapter.SaveEntity(overrun);
                                adapter.Commit();
                            }

                            how_to_update = UpdateType.Refresh;
                      
                        }
                        else
                        {
                            adapter.SaveEntity(entity);
                        }

                    }
                    else
                    {
                        adapter.SaveEntity(entity);
                    }

                    if (_on_production_history_change != null)
                        _on_production_history_change(how_to_update, start, end, -1);

                }
                catch (Exception ex)
                {
                    adapter.Rollback();
                    FileLog.Error("updateProductionHistoryEndTime() - ", ex);
                }
            }
        }


        // Update the EndTime of the ProductionHistoryEntity with the give ProductionHistoryID
        private void updateProductionHistoryReasonCode(int production_history_id, ReasonCodeEntity reason, int? production_plan_id)
        {
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    ProductionHistoryEntity entity = new ProductionHistoryEntity(production_history_id);
                    IPrefetchPath2 prefetch_path = new PrefetchPath2(VNet.VisiSuite.VisiBoard.Dal.EntityType.ProductionHistoryEntity);
                    prefetch_path.Add(ProductionHistoryEntity.PrefetchPathReasonCode);

                    adapter.FetchEntity(entity, prefetch_path);

                    DateTime start = entity.StartTime;
                    DateTime end = entity.EndTime ?? DateTime.Now;

                    entity.ReasonCodeId = (reason != null ? reason.ReasonCodeId : (int?)null);
                    entity.ProductionPlanId = production_plan_id;
                    entity.LastUpdated = DateTime.Now;
                    entity.SyncStatus = (byte)SyncStatus.OutOfSync;

                    adapter.SaveEntity(entity);

                    if (_on_production_history_change != null)
                    {
                        _on_production_history_change(UpdateType.UpdateLast, start, end, reason != null ? (int)reason.RollupDefinitionId : 0);
                    }
                        
                }
                catch (Exception ex)
                {
                    FileLog.Error("updateProductionHistoryReasonCode() - ", ex);
                }
            }
        }

        // Add a new ProductionHistoryDowntimeEventEntity with the given ProductionHistoryId, ReasonCodeId, and StartTime
        private ProductionHistoryDowntimeEvent addProductionHistoryDowntimeEvent(int parent_id, ReasonCodeEntity reason, DateTime start, int key, bool offline)
        {
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    var entity = new ProductionHistoryDowntimeEventEntity
                    {
                        ProductionHistoryId = parent_id,
                        ReasonCodeId = (reason != null ? reason.ReasonCodeId : (int?)null),
                        EventTime = start,
                        VisiBoxKey = key,
                        LastUpdated = DateTime.Now,
                        SyncStatus = (byte)SyncStatus.OutOfSync
                    };

                    adapter.SaveEntity(entity);
                }
                catch (Exception ex)
                {
                    FileLog.Error("addProductionHistoryDowntimeEvent() - ", ex);
                }
            }

            return new ProductionHistoryDowntimeEvent(parent_id, reason != null ? reason.ReasonCodeId : 0, start, key, offline);
        }


    }



}